package com.mrobuy.jdvc.service.impl;

import com.mrobuy.jdvc.dao.VCProductApplyDao;
import com.mrobuy.jdvc.entity.VCCategory;
import com.mrobuy.jdvc.entity.VCProductApply;
import com.mrobuy.jdvc.entity.VCShelvesItem;
import com.mrobuy.jdvc.service.ShelvesItemService;
import com.mrobuy.util.ExcelUtils;
import com.mrobuy.util.Pagination;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.List;


@Service("shelvesItemService")
public class ShelvesItemServiceImpl implements ShelvesItemService {

    private static Logger LOG = LoggerFactory.getLogger(ShelvesItemService.class);

    @Autowired
    private VCProductApplyDao vcProductApplyDao;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Pagination loadApplies(int start, int length, VCProductApply condition) {
        return vcProductApplyDao.load(start, length, condition);
    }

    @Override
    public void priceexport(OutputStream ouputStream) throws Exception {

        InputStream fis = getClass().getClassLoader().getResourceAsStream("template/price_template.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        fis.close();


        XSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());

        List<VCProductApply> applies = jdbcTemplate.query("select  * from vc_product_apply where STATE = '302' and ZT = '1' and ifnull(PRICEEXPORT, 'N') = 'N' ", new BeanPropertyRowMapper(VCProductApply.class));
        if ( applies!=null && applies.size() > 0 ) {
            int rowIndex = 3;

            for (VCProductApply apply : applies) {
                try {
                    List<VCShelvesItem>  items = jdbcTemplate.query("select * from VC_SHELVES_ITEM where APPLYID = ? ", new BeanPropertyRowMapper<>(VCShelvesItem.class), new Object[]{apply.getApplyId()});

                    if ( items!=null ) {
                        for (VCShelvesItem item : items) {
                            if ( item == null ) {
                                throw new RuntimeException("[ApplyId: " +  apply.getApplyId() + "]找不到对应的Item信息！");
                            }

                            VCCategory category = jdbcTemplate.queryForObject("select * from VC_CATEGORY where ID = ? ", new BeanPropertyRowMapper<>(VCCategory.class), item.getCid1());
                            if ( category == null ) {
                                throw new RuntimeException("[VC_CATEGORY][CID: " +   item.getCid1() + "]找不到对应的类目信息！");
                            }

                            ExcelUtils.setCellValue(sheet, rowIndex, 1, category.getPname());   // 二级类目
                            ExcelUtils.setCellValue(sheet, rowIndex, 2, category.getName());    // 三级类目
                            ExcelUtils.setCellValue(sheet, rowIndex, 6, item.getModel());       // 制造商型号
                            ExcelUtils.setCellValue(sheet, rowIndex, 8, item.getBrandName());   // 品牌
                            ExcelUtils.setCellValue(sheet, rowIndex, 9, item.getUnit());        // 计量单位

                            ExcelUtils.setCellValue(sheet, rowIndex, 10, "1");        // 最小箱规

                            //

                            ExcelUtils.setCellValue(sheet, rowIndex, 11, item.getPurchasePrice());  // 京东采购价
                            ExcelUtils.setCellValue(sheet, rowIndex, 12, item.getMarketPrice());    // 线下市场价
                            ExcelUtils.setCellValue(sheet, rowIndex, 13, item.getMemberPrice());    // 京东价（C端）

                            // "前台毛利率"	"大客户折扣"	大客户毛利率	友商价格1	友商价格2	商品重量（单位：kg 含包装)	长（单位：毫米 含包装）	宽（单位：毫米 含包装）	高（单位：毫米 含包装）	价格运费比例	备货时间



                            ExcelUtils.setCellValue(sheet, rowIndex, 21, item.getWeight());  // 商品重量（单位：kg 含包装)
                            ExcelUtils.setCellValue(sheet, rowIndex, 22, item.getLength());  // 长（单位：毫米 含包装）
                            ExcelUtils.setCellValue(sheet, rowIndex, 23, item.getWidth());  // 宽（单位：毫米 含包装）
                            ExcelUtils.setCellValue(sheet, rowIndex, 24, item.getHeight());  // 高（单位：毫米 含包装）

                            ExcelUtils.setCellValue(sheet, rowIndex, 26, item.getDeliverydate());  // 备货时间


                            ExcelUtils.setCellValue(sheet, rowIndex, 0, String.valueOf(rowIndex-2)); // 序号
                            ExcelUtils.setCellValue(sheet, rowIndex, 5, apply.getName());  // 产品名称

                            ExcelUtils.setCellFormula(sheet, rowIndex, 16, ((sheet1, row, cell) ->  String.format("IFERROR((N%d-L%d)/N%d,\"\")", row+1, row+1, row+1)) );  // 前台毛利率
                            ExcelUtils.setCellFormula(sheet, rowIndex, 17, ((sheet1, row, cell) -> String.format("IFERROR((P%d)/N%d,\"\")", row+1, row+1)) );  // 大客户折扣

                            for (int i = 0; i < 29; i++) {
                                ExcelUtils.applyCellStyle(sheet, rowIndex, i, ExcelUtils.getCellStyle(sheet, 3, i));
                            }

                            rowIndex++;
                        }

                    }

                } catch (Exception e) {
                    continue;
                }

                apply.setPriceexport("Y");
                apply.setPriceexporttime(Calendar.getInstance().getTime());
                vcProductApplyDao.update(apply, false);
            }


            XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
            formulaEvaluator.evaluateAll();


            // 结束吧活动cell定位在excel的第一列
            ExcelUtils.setCellValue(sheet, rowIndex, 0, "");
        }


        wb.write(ouputStream);
    }
}
